package binbase.web.gui

import javax.sql.DataSource
import static binbase.web.QueryUtil.*
import groovy.sql.Sql
import grails.converters.JSON

class BBExperimentSampleQueryController {


    DataSource dataSource

    def grailsApplication

    /**
     * list all samples as json
     */
    def ajaxListSamplesAsJSON = {
        def database = grailsApplication.config.binbase.database.toString()

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = sql.firstRow("select count(id) as id from bbexperiment_sample where id in (select distinct a.id from bbexperiment_sample a, bbexperiment_class b, bbexperiment c, bbdatabase d where a.experiment_class_id = b.id and b.experiment_id = c.id and c.database_id = d.id and d.name = ?)", [database]).id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
            select * from bbexperiment_sample where id in (select distinct a.id from bbexperiment_sample a, bbexperiment_class b, bbexperiment c, bbdatabase d where a.experiment_class_id = b.id and b.experiment_id = c.id and c.database_id = d.id and d.name = ?)
               """)


        buildQuery(params, query, [
                "id",
                "comment",
                "label",
                "file_name"
        ])

        sql.eachRow(query.toString(), [database], { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.comment,
                    bin.label,
                    bin.file_name
            ]

        })

        render dataToRender as JSON
    }
}
